﻿-- =============================================
-- Author:		GMH
-- Create date: 18/09/2012
-- Description:	
-- =============================================
CREATE PROCEDURE spObtenResultados 
	@pnIdRecibo			INT,
	@pnIdPaciente		INT,
	@ptFechaIni			DATE,
	@ptFechaFin			DATE,
	@pnIdLaboratorio	INT,
	@pnIdSucursal		INT
AS
BEGIN
	SET NOCOUNT ON;

    SELECT	r.IdRecibo,
			ISNULL(rs.IdEstatus,-1) AS IdEstatus,
			ISNULL(er.Descripcion,'Sin Resultado') AS DescripcionEstatus,
			rs.FechaRegistro AS FechaResultado,
			r.FechaRegistro AS FechaRecibo,
			r.IdPaciente,
			p.Nombre + ' ' + p.ApellidoPaterno + ' ' + ISNULL(p.ApellidoMaterno,'') AS NombrePaciente,
			rs.IdResultado
    FROM	dbo.Recibo		r	(NOLOCK)
    LEFT JOIN dbo.Resultado	rs	(NOLOCK)
    ON		r.IdRecibo		= rs.IdRecibo 
    AND		r.IdLaboratorio = rs.IdLaboratorio 
    AND		r.IdSucursal	= rs.IdSucursal
    LEFT JOIN dbo.CatPaciente p	(NOLOCK)
    ON		r.IdPaciente	= p.IdPaciente
    AND		r.IdLaboratorio= p.IdLaboratorio
    AND		r.IdSucursal	= p.IdSucursal
    LEFT JOIN dbo.CatEstatusResultado	er (NOLOCK)
    ON		rs.IdEstatus	= er.IdEstatusResultado
    WHERE	(r.IdRecibo		= @pnIdRecibo OR @pnIdRecibo IS NULL)
    AND		(r.IdPaciente	= @pnIdPaciente OR @pnIdPaciente IS NULL)
    AND		r.FechaRegistro BETWEEN ISNULL(@ptFechaIni,CAST('19000101' AS DATE)) AND ISNULL(@ptFechaFin,CAST(GETDATE() + 1 AS DATE))
    AND		r.IdLaboratorio = @pnIdLaboratorio
    AND		r.IdSucursal	= @pnIdSucursal
    AND		r.IdEstatus		IN (2,3)

    	
	SET NOCOUNT OFF;
END